﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SharpHsql.Data;
using SharpHsql.Data.Client;

namespace WinTest
{
    public partial class Form1 : Form
    {
        Database db = new Database(".");
        Channel channel;

        public Form1()
        {
            InitializeComponent();
            channel = db.Connect("sa", "");
        }

        private void btnCreateTable_Click(object sender, EventArgs e)
        {
            string script = @"Create Table Product(ID INT PRIMARY KEY,Name VarChar(50),CategoryId INT,Price Decimal )";
            var rs = db.Execute(script, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);

            script = @"Create Table Category(ID INT PRIMARY KEY,Name VarChar(50) )";
            rs = db.Execute(script, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);

            script = @"Create Table Orders(ID INT PRIMARY KEY,OrderDate Varchar,CustomerId INT)";
            rs = db.Execute(script, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);

            script = @"Create Table OrderDetails(ID INT PRIMARY KEY,OrderID INT,ProductId INT,Count INT)";
            rs = db.Execute(script, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);

            Console.WriteLine("ready.");
         
        }

        private void btnInitData_Click(object sender, EventArgs e)
        {
            string script;
            var rd = new Random();
            Result rs;
            for (int i = 1; i < 100001; i++)
            {
                script = string.Format("Insert Into Product Values( {0} ,'Product_{1}',{2},{3} )", i, i, rd.Next(1, 100), rd.Next(10, 1000) / 100);
                rs = db.Execute(script, channel);
                if (rs.Error != null)
                    Console.WriteLine(rs.Error);
            }

            for (int i = 1; i < 10001; i++)
            {
                script = string.Format("Insert Into Orders Values( {0} ,'{1}',{2} )", i, DateTime.Now, rd.Next(1, 10000));
                rs = db.Execute(script, channel);
                if (rs.Error != null)
                    Console.WriteLine(rs.Error);
            }

            for (int i = 1; i < 201; i++)
            {
                script = string.Format("Insert Into Category Values( {0} ,'Category_{0}')", i);
                rs = db.Execute(script, channel);
                if (rs.Error != null)
                    Console.WriteLine(rs.Error);
            }

            for (int i = 1; i < 50001; i++)
            {
                script = string.Format("Insert Into OrderDetails Values( {0} ,{1},{2},{3} )", i, rd.Next(1, 100000), rd.Next(1, 10000), rd.Next(2, 10));
                rs = db.Execute(script, channel);
                if (rs.Error != null)
                    Console.WriteLine(rs.Error);
            }


            Console.WriteLine("ready.");
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            var script = @"Select Product.ID ,Product.Name ,Category.Name As CategoryName ,OrderDetails.Count ,Orders.OrderDate
                                     From Product 
                                     Inner Join Category On Product.CategoryId = Category.ID
                                     Inner Join OrderDetails On OrderDetails.ProductId = Product.ID
                                     Inner Join Orders On Orders.ID = OrderDetails.OrderId
                                     Where Orders.ID > 1 And Orders.ID < 100";
            var rs = db.Execute(script, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);
            if (rs.Root != null)
            {
                Record r = rs.Root;
                int column_count = rs.ColumnCount;

                for (int x = 0; x < column_count; x++)
                {
                    Console.Write(rs.Label[x]);
                    Console.Write("\t\t");
                }
                Console.Write("\n");

                while (r != null)
                {
                    for (int x = 0; x < column_count; x++)
                    {
                        Console.Write(r.Data[x]);
                        Console.Write("\t\t");
                    }
                    Console.Write("\n");
                    r = r.Next;
                }
                Console.Write("\n");

            }
        }

        private void btnIndex_Click(object sender, EventArgs e)
        {
            var query = "CREATE INDEX iProduct ON Product(CategoryId)";
            var rs = db.Execute(query, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);


            query = "CREATE INDEX iOrderId ON OrderDetails(OrderId)";
             rs = db.Execute(query, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);

            query = "CREATE INDEX iProductId ON OrderDetails(ProductId)";
             rs = db.Execute(query, channel);
            if (rs.Error != null)
                Console.WriteLine(rs.Error);

            Console.WriteLine("ready.");
        }


    }
}
